package com.huatai.datacenter.service;

import com.alibaba.druid.pool.DruidPooledConnection;
import com.alibaba.fastjson.JSONObject;
import com.huatai.datacenter.config.PoolManager;
import com.huatai.datacenter.entity.SyncTableEntity;
import com.huatai.datacenter.mapper.DataSourceMapper;
import com.huatai.datacenter.entity.DataSourceEntity;
import com.huatai.datacenter.utils.CryptoUtil;
import com.huatai.datacenter.utils.JDBCUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Lion
 * @date 2023/3/13  10:14
 */
@Slf4j
@Service
public class DataSourceService extends BaseService<DataSourceMapper, DataSourceEntity> {

	@Autowired
	private DataSourceMapper dataSourceMapper;

	@Autowired
	private SyncTaskService syncTaskService;

	public DataSourceEntity detail(String id) {
		DataSourceEntity dataSource = dataSourceMapper.selectById(id);
		return dataSource;
	}

	/**
	 * @author Lion
	 * @description 测试数据库连接
	 * @param dataSource 数据源信息
	 * @return boolean
	 */
	public boolean connect(DataSourceEntity dataSource){

		try {
			Class.forName(dataSource.getDriver());
			//判断是否携带id
			if (dataSource.getId() != null){
				//携带id 判断密码是否修改过
				String desPassword = this.getById(dataSource.getId()).getPassword();
				if (desPassword.equals(dataSource.getPassword())){
					DriverManager.getConnection(dataSource.getUrl(),dataSource.getUsername(),CryptoUtil.decode(dataSource.getPassword()));
				}else {
					DriverManager.getConnection(dataSource.getUrl(),dataSource.getUsername(),dataSource.getPassword());
				}
			}else {
				DriverManager.getConnection(dataSource.getUrl(),dataSource.getUsername(),dataSource.getPassword());
			}
			return true;
		} catch (ClassNotFoundException | SQLException e) {
			return false;
		}

	}

	/**
	 * @author Lion
	 * @description 根据数据源id获取所有表名
	 * @param  dataSourceId
	 * @return java.util.List<java.lang.String>
	 */
	public List<String> queryAllTables(String dataSourceId){

		List<String> tables = null;
		try {
			DataSourceEntity dataSource = super.getById(dataSourceId);
			Class.forName(dataSource.getDriver());
			Connection connection = DriverManager.getConnection(dataSource.getUrl(),dataSource.getUsername(), CryptoUtil.decode(dataSource.getPassword()));
			tables = JDBCUtil.getAllTables(connection,dataSource.getTableSql());

		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}

		return tables;
	}

	/**
	 * @author Lion
	 * @description 根据数据源id和表名获取所有列名
	 * @param dataSourceId
	 * @param tableName
	 * @return java.util.List<com.alibaba.fastjson.JSONObject>
	 */
	public List<JSONObject> getALLColumns(long dataSourceId,String tableName){

		List<JSONObject> columns = null;
		DataSourceEntity dataSource = super.getById(dataSourceId);
		try {
			Class.forName(dataSource.getDriver());
			Connection connection = DriverManager.getConnection(dataSource.getUrl(), dataSource.getUsername(), CryptoUtil.decode(dataSource.getPassword()));

			columns = JDBCUtil.getRDBMSColumnProperties(connection,dataSource.getDataSourceType(),tableName);
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		return columns;


	}

	public void copyDataTable(JSONObject data) throws Exception {

		long dataSourceId = data.getLong("dataSourceId");
		String tableName = data.getString("tableName");
		long targetDataSourceId = data.getLong("dataSourceId");

		DataSourceEntity dataSource1 = super.getById(dataSourceId);
		DataSourceEntity dataSource2 = super.getById(targetDataSourceId);

		Class.forName(dataSource2.getDriver());
		Connection connection = DriverManager.getConnection(dataSource2.getUrl(),dataSource2.getUsername(),dataSource2.getPassword());

		String sql4 = "create table " + dataSource2.getDatabaseName() + "." + tableName + "_" + getDateTimeString() + "  select * from " + dataSource1.getDatabaseName() + "." + tableName;

		JDBCUtil.setupdateData(sql4,connection);
	}

	public static String getDateTimeString()
	{
		return DateFormatUtils.format(System.currentTimeMillis(), "yyyyMMddHHmmss");
	}

	public void moveTableData(JSONObject data) throws SQLException {

		long dataSourceId = data.getLong("dataSourceId");
		String tableName = data.getString("tableName");
		long targetDataSourceId = data.getLong("targetId");

		//发送者
		DataSourceEntity dataSource1 = super.getById(dataSourceId);
		//接收者
		DataSourceEntity dataSource2 = super.getById(targetDataSourceId);
		// 建立发送者连接
		Connection conSend = JDBCUtil.getConnection(dataSource1);//DriverManager.getConnection(dataSource1.getUrl(),dataSource1.getUsername(),dataSource1.getPassword());
		Statement stmtSend = conSend.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

		// 拿到查询sql
		String sql = "select * from " + tableName;

		try {
			// 通过拼接好的sql查询数据并保存到结果集
			ResultSet send = stmtSend.executeQuery(sql);
			if (send == null) {
				// 结果集为空说明没有新数据，不进行后续操作
				System.out.println("没有需要进行迁移的数据");
				return;
			}

			//获取表结构
			DatabaseMetaData metaData1 = conSend.getMetaData();
			ResultSet columnResultSet = metaData1.getColumns(null, null, tableName, null);
			//获取主键
			ResultSet pkRSet = metaData1.getPrimaryKeys( null, null, tableName);
			String pk = "";
			while( pkRSet.next() ) {
				pk = String.valueOf(pkRSet.getObject(4));
			}
			//获取表结构
			String sql4 = transfer(tableName,columnResultSet,pk);

			// 建立接收者连接
			Connection conAccept = JDBCUtil.getConnection(dataSource2);
			Statement stmtAccept = conAccept.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

			//表创建
			stmtAccept.execute(sql4);

			String sql5 = "  select * from " + dataSource2.getDatabaseName() + "." + tableName;
            // 定位接收表
			ResultSet accept =  stmtAccept.executeQuery(sql5);

			// 遍历发送者结果集
			while (send.next()) {
				// 开启新增
				accept.moveToInsertRow();
				// 获取当前表结构
				ResultSetMetaData metaData = send.getMetaData();
				// 通过表结构字段数遍历并设置当前行数据
				for (int i = 1; i <= metaData.getColumnCount(); i++) {
					accept.updateString(metaData.getColumnLabel(i), send.getString(i));
				}
				// 保存新增内容
				accept.insertRow();
			}
			conSend.close();
			conAccept.close();
			// 有异常时打印异常信息
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return;
		}
		SyncTableEntity syncTable = new SyncTableEntity();
		syncTable.setSyncTableName(tableName);
		syncTable.setDataSourceId(dataSourceId);
		syncTable.setTargetSourceId(targetDataSourceId);
		syncTable.setSyncTaskName(data.getString("syncTaskName"));
		syncTaskService.save(syncTable);
	}

	/**
	 * @author Lion
	 * @description 根据表名拼接建表语句
	 * @param objectTableName
	 * @param columnResultSet
	 * @param pk
	 * @return java.lang.String
	 */
	public String transfer(String objectTableName, ResultSet columnResultSet,String pk) throws SQLException {
		StringBuilder start = new StringBuilder().append("CREATE TABLE ")
			.append(objectTableName).append("(");
		StringBuilder builder = new StringBuilder();
		List<String> column = new ArrayList<>();

		//获取字段属性
		while (columnResultSet.next()) {

			String field_name = columnResultSet.getString("COLUMN_NAME");
			if (column.contains(field_name)){
				break;
			}
			column.add(field_name);
			String field_type = columnResultSet.getString("TYPE_NAME");
			int field_length = columnResultSet.getInt("COLUMN_SIZE");
			int field_is_null = columnResultSet.getInt("NULLABLE");
			String remarks = columnResultSet.getString("REMARKS");
			builder.append("`");
			builder.append(field_name).append("`").append(" ");

			//如果为时间类型  长度为3
			if ("DATETIME".equals(field_type)){
				builder.append(field_type).append("(").append(3).append(")").append(" ");
			}else {
				if (field_type.contains("UNSIGNED")){
					String fsd = field_type.substring(0, field_type.lastIndexOf("U"));
					builder.append(fsd).append("(").append(field_length).append(")").append(" ");
				}else {
					builder.append(field_type).append("(").append(field_length).append(")").append(" ");
				}
			}
			if (field_is_null == 1) {
				builder.append(" DEFAULT NULL ");
			} else {
				builder.append(" NOT NULL ");
			}
			builder.append("COMMENT").append(" ").append("'").append(remarks).append("'");

			builder.append(",");

		}
		builder.append("PRIMARY KEY (`").append(pk).append("`) USING BTREE");
		StringBuilder end = new StringBuilder().append(")").append("ENGINE=InnoDB DEFAULT CHARSET=utf8").append(" ")
			.append("COMMENT=").append("'").append("").append("'");
		StringBuilder sqlString = start.append(builder).append(end);
		return sqlString.toString();
	}
}
